--EXEC SP_WAFER_START_REPORT_IKANOS 1
alter PROC SP_WAFER_START_REPORT_IKANOS  @FAB_PROCESSSTEP_ID_INPUT INT
AS
BEGIN
	CREATE TABLE #FORECAST_TABLE
	(
		FG_PART_NUMBER VARCHAR(50), 
		FG_PS_ID INT,
		CQ_SHIPPED_QTY INT, --CURRENT QTR SHIPPED QTY
		CQ_FORECAST_QTY INT, --CURRENT QTR FORECAST QTY
		Q1_FORECAST_QTY INT, --NEXT QTR FORECAST QTY
		Q2_FORECAST_QTY INT, --NEXT NEXT QTR FORECAST QTY
		Q3_FORECAST_QTY INT, --NEXT NEXT NEXT QTR FORECAST QTY
	)

	CREATE TABLE #RESULT_TABLE
	(
		FAB_PART_NUMBER VARCHAR(50),
		FG_PART_NUMBER VARCHAR(50), 
		CHIP_SET VARCHAR(50),
		Product_Line VARCHAR(50),
		Chip_Family VARCHAR(50),
		Process VARCHAR(50),
		FG_PS_ID INT,
		CQ_SHIPPED_QTY INT, --CURRENT QTR SHIPPED QTY
		CQ_FORECAST_QTY INT, --CURRENT QTR FORECAST QTY
		Q1_FORECAST_QTY INT, --NEXT QTR FORECAST QTY
		Q2_FORECAST_QTY INT, --NEXT NEXT QTR FORECAST QTY
		Q3_FORECAST_QTY INT, --NEXT NEXT NEXT QTR FORECAST QTY
		CQ_BACKLOG_QTY INT,
		FG_MIN_LEVEL_QTY INT,
		FAB_OPEN_PO_QTY INT,
		FAB_QTY INT,
		WAFER_STORE_QTY INT,
		SORT_QTY INT,
		DIE_BANK_QTY INT,
		ASSEMBLY_QTY INT,
		PRE_TEST INT,
		TEST_QTY INT,
		EOL_QTY INT,
		FG_QTY INT,
		FAB_YIELD FLOAT,
		SORT_YIELD FLOAT,
		ASSEMBLY_YIELD FLOAT,
		TEST_YIELD FLOAT,
		Fab_CYCLE_TIME INT,
		Sort_CYCLE_TIME INT,
		Assembly_CYCLE_TIME INT,
		TEST_CYCLE_TIME INT,
		EOL_CYCLE_TIME INT,
		GROSS_DIE_PER_WAFER INT,
		Wafer_Cost FLOAT,
		DIES_PER_PACKAGE INT
	)

	DECLARE @CURR_QTR_START_DATE DATETIME
	DECLARE @CURR_QTR_END_DATE DATETIME
	DECLARE @CURR_QTR_NO VARCHAR(20)
	DECLARE @CURR_QTR INT
	DECLARE @CURR_YEAR INT
	DECLARE @NEXT_QTR INT
	DECLARE @NEXT_YEAR INT
	DECLARE @FORECAST_CURSOR CURSOR
	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @PROCESSSTEP_CURSOR CURSOR
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @PROCESS VARCHAR(50)
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @Wafer_Cost FLOAT
	
	SELECT @CURR_QTR_START_DATE = CQC_START_DATE, @CURR_QTR_END_DATE = CQC_END_DATE, @CURR_QTR_NO = CQC_QUARTER 
	FROM Company_Quarterly_Calendar WHERE CQC_START_DATE <= CURRENT_TIMESTAMP AND CQC_END_DATE  >= CURRENT_TIMESTAMP

	SET @CURR_QTR = CONVERT(INT, SUBSTRING ( @CURR_QTR_NO,len(@CURR_QTR_NO),1))
	SET @CURR_YEAR = YEAR(CURRENT_TIMESTAMP)

	INSERT INTO #FORECAST_TABLE (FG_PART_NUMBER, FG_PS_ID, CQ_FORECAST_QTY)
	SELECT P.P_MARKETING_PART_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCENTRYMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @CURR_YEAR AND QUARTER = @CURR_QTR
	GROUP BY P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER

	INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY)
	SELECT P.P_MARKETING_PART_NUMBER, P.PROCESSSTEP_ID, SUM(CSLIT_QTY_SHIPPED) FROM
	CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P
	WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND CSLIT_SHIPPED_DATE >= @CURR_QTR_START_DATE
	GROUP BY P.P_MARKETING_PART_NUMBER, P.PROCESSSTEP_ID

	PRINT 'Q1...................'
	IF( @CURR_QTR + 1 > 4 )
	BEGIN
		SET @NEXT_QTR = ( @CURR_QTR + 1 ) - 4
		SET @NEXT_YEAR = @CURR_YEAR + 1
	END
	ELSE
	BEGIN
		SET @NEXT_QTR = @CURR_QTR + 1
		SET @NEXT_YEAR = @CURR_YEAR
	END
	PRINT @NEXT_QTR 
	PRINT @NEXT_YEAR 

	INSERT INTO #FORECAST_TABLE (FG_PART_NUMBER, FG_PS_ID, Q1_FORECAST_QTY)
	SELECT P_MARKETING_PART_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCENTRYMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @NEXT_YEAR AND QUARTER = @NEXT_QTR
	GROUP BY P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER

	PRINT 'Q2...................'
	IF( @CURR_QTR + 2 > 4 )
	BEGIN
		SET @NEXT_QTR = ( @CURR_QTR + 2 ) - 4
		SET @NEXT_YEAR = @CURR_YEAR + 1
	END
	ELSE
	BEGIN
		SET @NEXT_QTR = @CURR_QTR + 2
		SET @NEXT_YEAR = @CURR_YEAR
	END
	PRINT @NEXT_QTR 
	PRINT @NEXT_YEAR 

	INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, Q2_FORECAST_QTY)
	SELECT P_MARKETING_PART_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCENTRYMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @NEXT_YEAR AND QUARTER = @NEXT_QTR
	GROUP BY P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER
	
	PRINT 'Q3...................'
	IF( @CURR_QTR + 3 > 4 )
	BEGIN
		SET @NEXT_QTR = ( @CURR_QTR + 3 ) - 4
		SET @NEXT_YEAR = @CURR_YEAR + 1
	END
	ELSE
	BEGIN
		SET @NEXT_QTR = @CURR_QTR + 3
		SET @NEXT_YEAR = @CURR_YEAR
	END
	PRINT @NEXT_QTR 
	PRINT @NEXT_YEAR 

	INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, Q3_FORECAST_QTY)
	SELECT P_MARKETING_PART_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCENTRYMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @NEXT_YEAR AND QUARTER = @NEXT_QTR
	GROUP BY P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER

	INSERT INTO #RESULT_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, Q1_FORECAST_QTY, Q2_FORECAST_QTY, Q3_FORECAST_QTY )
	SELECT FG_PART_NUMBER, FG_PS_ID, SUM(CQ_SHIPPED_QTY), SUM(CQ_FORECAST_QTY), SUM(Q1_FORECAST_QTY), SUM(Q2_FORECAST_QTY), SUM(Q3_FORECAST_QTY) FROM #FORECAST_TABLE
	GROUP BY FG_PART_NUMBER, FG_PS_ID

	DECLARE @FAB_QTY INT
	DECLARE @WAFERSTORE_QTY INT
	DECLARE @SORT_QTY INT
	DECLARE @DIEBANK_QTY INT
	DECLARE @ASSEMBLY_QTY INT
	DECLARE @PRETEST_QTY INT
	DECLARE @TEST_QTY INT
	DECLARE @EOL_QTY INT
	DECLARE @FG_QTY INT
	DECLARE @TRANSIT_QTY INT
	DECLARE @FG_MIN_LEVEL_QTY INT
	DECLARE @GROSS_DIE_PER_WAFER INT
	DECLARE @FAB_PART_NUMBER VARCHAR(50)
	DECLARE @FAB_YIELD FLOAT
	DECLARE @SORT_YIELD FLOAT
	DECLARE @ASSEMBLY_YIELD FLOAT
	DECLARE @TEST_YIELD FLOAT
	DECLARE @CHIPSET VARCHAR(50)
	DECLARE @FAB_CYCLE_TIME INT	
	DECLARE @SORT_CYCLE_TIME INT	
	DECLARE @ASSEMBLY_CYCLE_TIME INT	
	DECLARE @TEST_CYCLE_TIME INT	
	DECLARE @EOL_CYCLE_TIME INT	
	DECLARE @OPEN_PO_QTY INT
	DECLARE @CHIP_FAMILY VARCHAR(50)
	DECLARE @PRODUCT_LINE VARCHAR(50)
	DECLARE @CQ_BACKLOG_QTY INT
	DECLARE @DIES_PER_PACKAGE INT

	SET	@FORECAST_CURSOR = CURSOR FOR SELECT FG_PS_ID FROM #RESULT_TABLE
	OPEN @FORECAST_CURSOR
	FETCH NEXT FROM @FORECAST_CURSOR INTO @FG_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @FAB_QTY = 0
		SET @WAFERSTORE_QTY = 0
		SET @SORT_QTY = 0
		SET @DIEBANK_QTY = 0
		SET @ASSEMBLY_QTY = 0
		SET @PRETEST_QTY = 0
		SET @TEST_QTY = 0
		SET @EOL_QTY = 0
		SET @FG_QTY = 0
		SET @GROSS_DIE_PER_WAFER = 0
		SET @FAB_PART_NUMBER = ''
		SET @FAB_YIELD = 0.0
		SET @SORT_YIELD = 0.0
		SET @ASSEMBLY_YIELD = 0.0
		SET @TEST_YIELD = 0.0
		SET @FAB_CYCLE_TIME = 0	
		SET @SORT_CYCLE_TIME = 0		
		SET @ASSEMBLY_CYCLE_TIME = 0		
		SET @TEST_CYCLE_TIME = 0		
		SET @EOL_CYCLE_TIME = 0		
		SET @OPEN_PO_QTY = 0
		SET @CHIPSET = ''
		SET @CHIP_FAMILY = ''
		SET @PRODUCT_LINE = ''
		SET @DIES_PER_PACKAGE = 1

		SET @PROCESSSTEP_CURSOR = CURSOR FOR SELECT ProcessStep_Id, PS_Type, PS_Type_Name FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @FG_PROCESSSTEP_ID, 0 )
		OPEN @PROCESSSTEP_CURSOR
		FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @PS_TYPE_NAME
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @TRANSIT_QTY = 0
			IF (@PS_TYPE = 'Fab' )-- OR @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'SORT')
			BEGIN
				SELECT @OPEN_PO_QTY = SUM(Requested_Qty)-SUM(Received_Qty) FROM PurchaseOrder_Line_Item_Table WHERE ProcessStep_Id = @PROCESSSTEP_ID AND Status = 'APPROVED'
				SELECT @FAB_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FAB_QTY = SUM(WAFER_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @PROCESS = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Fab Process' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @Wafer_Cost = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @FAB_QTY = ISNULL(@FAB_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				SET @OPEN_PO_QTY = ISNULL( @OPEN_PO_QTY, 0 ) - @FAB_QTY
				IF @OPEN_PO_QTY < 0
				BEGIN
					SET @OPEN_PO_QTY = 0
				END
				SELECT @GROSS_DIE_PER_WAFER = ISNULL( CONVERT( INT, Parameter_Value), 1) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Gross Die/Wafer' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FAB_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FAB_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				IF ( @FAB_YIELD IS NULL OR @FAB_YIELD <= 0.0 )
				BEGIN
					SET @FAB_YIELD = 100.00
				END
			END 
			ELSE IF (@PS_TYPE = 'Wafer Bank' )
			BEGIN
				SELECT @WAFERSTORE_QTY = SUM(WAFER_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @WAFERSTORE_QTY = ISNULL(@WAFERSTORE_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'SORT' )
			BEGIN
				--SELECT @SORT_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				SELECT @SORT_YIELD = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @SORT_QTY = SUM(WAFER_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @SORT_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @SORT_QTY = ISNULL(@SORT_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				IF ( @SORT_YIELD IS NULL OR @SORT_YIELD <= 0.0 )
				BEGIN
					SET @SORT_YIELD = 100.00
				END
			END
			ELSE IF (@PS_TYPE = 'Die Bank' )
			BEGIN
				SELECT @DIEBANK_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @DIEBANK_QTY = ISNULL(@DIEBANK_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'Assembly' )
			BEGIN
				--SELECT @ASSEMBLY_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				SELECT @ASSEMBLY_YIELD = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @DIES_PER_PACKAGE = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Dies/Package' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @ASSEMBLY_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @ASSEMBLY_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @ASSEMBLY_QTY = ISNULL(@ASSEMBLY_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				
				IF ( @ASSEMBLY_YIELD IS NOT NULL AND @ASSEMBLY_YIELD <= 1.0 )
				BEGIN
					SET @ASSEMBLY_YIELD = @ASSEMBLY_YIELD * 100
				END

				IF ( @ASSEMBLY_YIELD IS NULL OR @ASSEMBLY_YIELD <= 0.0 )
				BEGIN
					SET @ASSEMBLY_YIELD = 100.00
				END
			END
			ELSE IF (@PS_TYPE = 'Pre Test' )
			BEGIN
				SELECT @PRETEST_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @PRETEST_QTY = ISNULL(@PRETEST_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'FT' )
			BEGIN
				--SELECT @TEST_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				SELECT @TEST_YIELD = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @TEST_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TEST_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @TEST_QTY = ISNULL(@TEST_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				IF ( @TEST_YIELD IS NULL OR @TEST_YIELD <= 0.0 )
				BEGIN
					SET @TEST_YIELD = 100.00
				END
			END
			ELSE IF (@PS_TYPE = 'EOL' )
			BEGIN
				SELECT @EOL_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @EOL_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @EOL_QTY = ISNULL(@EOL_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'FG' )
			BEGIN
				SELECT @FG_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @CHIPSET = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Chipset1' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FG_MIN_LEVEL_QTY = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Reorder Level' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @CHIP_FAMILY = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Chip Family' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @PRODUCT_LINE = PRODUCT_GROUP FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @CQ_BACKLOG_QTY = SUM(COOL_QTY) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @PROCESSSTEP_ID AND COOL.COOL_STATUS <> 'SHIPPED' AND COOL.COOL_REQUESTED_DATE <= @CURR_QTR_END_DATE
			END
			FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @PS_TYPE_NAME
		END
		IF(@FAB_YIELD <= 0.0 )
			SET @FAB_YIELD = 100.00
		IF(@ASSEMBLY_YIELD <= 0.0 )
			SET @ASSEMBLY_YIELD = 100.00
		IF(@TEST_YIELD <= 0.0 )
			SET @TEST_YIELD = 100.00
		IF(@SORT_YIELD <= 0.0 )
			SET @SORT_YIELD = 100.00
		
		UPDATE #RESULT_TABLE SET FAB_OPEN_PO_QTY = @OPEN_PO_QTY, FAB_QTY = @FAB_QTY, WAFER_STORE_QTY = @WAFERSTORE_QTY, SORT_QTY = @SORT_QTY, DIE_BANK_QTY = @DIEBANK_QTY, ASSEMBLY_QTY = @ASSEMBLY_QTY, PRE_TEST = @PRETEST_QTY, 
		TEST_QTY = @TEST_QTY, EOL_QTY = @EOL_QTY, FG_QTY = @FG_QTY, GROSS_DIE_PER_WAFER = @GROSS_DIE_PER_WAFER, FAB_PART_NUMBER = @FAB_PART_NUMBER,
		FAB_YIELD = @FAB_YIELD/100, SORT_YIELD = @SORT_YIELD/100, ASSEMBLY_YIELD = @ASSEMBLY_YIELD/100, TEST_YIELD = @TEST_YIELD/100, CHIP_SET = @CHIPSET,
		Fab_CYCLE_TIME = @FAB_CYCLE_TIME, Sort_CYCLE_TIME = @SORT_CYCLE_TIME, Assembly_CYCLE_TIME = @ASSEMBLY_CYCLE_TIME, TEST_CYCLE_TIME = @TEST_CYCLE_TIME, EOL_CYCLE_TIME = @EOL_CYCLE_TIME,
		Chip_Family = @CHIP_FAMILY, Product_Line = @PRODUCT_LINE, Process = @PROCESS, Wafer_Cost = @Wafer_Cost, FG_MIN_LEVEL_QTY = @FG_MIN_LEVEL_QTY, CQ_BACKLOG_QTY = @CQ_BACKLOG_QTY, DIES_PER_PACKAGE = @DIES_PER_PACKAGE
		WHERE FG_PS_ID = @FG_PROCESSSTEP_ID

		CLOSE @PROCESSSTEP_CURSOR
		DEALLOCATE @PROCESSSTEP_CURSOR
		FETCH NEXT FROM @FORECAST_CURSOR INTO @FG_PROCESSSTEP_ID
	END
	CLOSE @FORECAST_CURSOR
	DEALLOCATE @FORECAST_CURSOR
	
	SELECT * FROM #RESULT_TABLE ORDER BY FAB_PART_NUMBER, FG_PART_NUMBER
END